'''
Author: your name
Date: 2021-03-23 09:41:58
LastEditTime: 2022-05-22 14:06:12
LastEditors: 风云 mayude@live.cn
Description: In User Settings Edit
FilePath: /tinyms/tinyms-api/apps/auth/census/service.py
'''
import json
from app import db

class CensusService():
    def census_organ():
        # sql="""
        #     SELECT a.dict_name AS qhmc,a.dict_code AS qhdm,a.dict_level AS qhjb,count(b.organ_pk) AS yhNum FROM (
        #     SELECT DISTINCT dict_code,(substr(dict_name,0,2) || substr(dict_name,length(dict_name),1)) AS dict_name,dict_level FROM sys_dict WHERE dict_type='XZQH' AND dict_status='1') a 
        #     LEFT JOIN (SELECT*FROM sys_organ WHERE organ_status='1') b ON a.dict_code=b.organ_code GROUP BY a.dict_code,a.dict_name,a.dict_level ORDER BY a.dict_code ASC
        #     """
        sql = """
                SELECT
                organ_code AS code,organ_name AS name ,count( * ) AS count 
                FROM sys_organ GROUP BY CODE,NAME
              """
        res = db.session.execute(sql)
        all_res_list = res.fetchall()
        return all_res_list
    "统计机构下的用户数"
    def census_organ_user():
        sql = """
            select t.organ_code AS code,
	        t.organ_name AS name,
			count( * ) AS count  from (select * from sys_user as u LEFT JOIN sys_organ as o ON u.fk_role_pk = o.organ_pk) t
			GROUP BY t.organ_code,t.organ_name
            """
        res = db.session.execute(sql)
        all_res_list = res.fetchall()
        return all_res_list


    def count_log():
        sql = """
            select count(*) from sys_log;
            """
        res = db.session.execute(sql)
        all_res_list = res.fetchall()
        return all_res_list